package com.cly.house.uitl;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;



public class JdbcUtil {
	private static String driver = null;
	private static String url = null;
	private static String user = null;
	private static String pwd = null;
	
	
	static {
		try {
			InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
			Properties pro = new Properties();
			pro.load(is);
			driver = pro.getProperty("driver");
			url = pro.getProperty("url");
			user = pro.getProperty("user");
			pwd  = pro.getProperty("password");
			try {
				Class.forName(driver);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public static Connection getConn() {
		Connection conn = null;
		
		try {
			conn = DriverManager.getConnection(url, user, pwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
		
	}
	
	public static void closeConnection(Connection conn) {
		try {
			if(conn!=null) {
				conn.close();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void closeAll(Connection conn,Statement ps,ResultSet rs) {
		try {
			if(rs!=null)
				rs.close();
			if(ps!=null)
				ps.close();
			if(conn!=null) 
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void closeAll(Connection conn,Statement ps) {
		closeAll(conn, ps, null);
	}
	
	public static <T> List<T> findBypage(String sql,int pageNum,int pageSize, Class<T> clazz,Object...params) throws SecurityException, Exception {
		int from = (pageNum-1)*pageSize;
		sql += " limit "+from +","+pageSize;
		return executeQuery(sql, clazz, params);
	}
	
	public static int total (String sql,Object ...params) {
		int total = 0;
		Connection conn = null;
		conn = getConn();
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sql);
			setParamValues(ps, params);
			ResultSet rs = ps.executeQuery();
			rs.next();
			total =rs.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeConnection(conn);
		}
		
		return total;
	}
		
	public static <T> List<T> executeQuery(String sql, Class<T> clazz,Object...params) throws Exception, SecurityException{
		Connection conn = null;
		List<T> list = new ArrayList<T>();
		
		
		try {
			conn = getConn();
			PreparedStatement ps = conn.prepareStatement(sql);
			setParamValues(ps, params);
			ResultSet rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int clnCount = rsmd.getColumnCount();
			while(rs.next()) {
				T entity= null;
				entity = clazz.newInstance();
				for (int i = 0; i < clnCount; i++) {
					
					String clnName = rsmd.getColumnName(i+1);
					Object clnValue = rs.getObject(clnName);
					Field field = clazz.getDeclaredField(clnName.toLowerCase());
					field.setAccessible(true);
					field.set(entity,clnValue );
				}
				list.add(entity);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeConnection(conn);
		}
		
		return list;
	}
	
	public static int executeUpdate(String sql,Object...params) {
		int count = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = getConn();
			ps = conn.prepareStatement(sql);
			setParamValues(ps, params);
			count = ps.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(conn, ps);
		}
		return count;
		
	}
	private static void setParamValues(PreparedStatement ps,Object...params) {
		try {
			if(params!=null&&params.length>0) {
				for (int i = 0; i < params.length; i++) {
					ps.setObject(i+1, params[i]);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}
